package com.scalar.chiptrack.operations.lotcorrection.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.operations.lotcorrection.LotCorrectionList;
import com.scalar.chiptrack.operations.lotcorrection.LotCorrection;
import com.scalar.chiptrack.operations.lotcorrection.PackingSlipLineItem;
import com.scalar.chiptrack.operations.packingslip.CustomerShippingLineItems;
import com.scalar.chiptrack.operations.packingslip.forms.PackingSlipForm;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.utils.SqlQueryMappings;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

/**
 * Contains data access method pertaining to the lot correction screen
 *
 * Author  : rohan
 * Date    : Jul 28, 2004
 * Version : 1.0
 */
public class LotCorrectionDAO {

    /**
     * Returns a list of lot correction objects pertaining to the lots existing in the
     * next immmediate stage of the present stage of the given lot
     *
     * @param lotId
     * @return
     * @throws ScalarException
     */
    public static LotCorrectionList getLotsInNextImmediateStage(DBTransaction dbTransaction, String lotId) throws ScalarException {
        LotCorrectionList lotCorrectionList = new LotCorrectionList();
        LotCorrection lotCorrection = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection(Constants.DATASOURCE_TYPE_LOCAL);
            } else {
                conn = dbTransaction.getConnection(Constants.DATASOURCE_TYPE_LOCAL);
            }

            pstmt = conn.prepareStatement("{call  sp_get_lot_in_next_stage( ? ) }");
            pstmt.setString(1, lotId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                lotCorrection = new LotCorrection();
                lotCorrection.setLotId(rs.getString("Lot_Id"));
                lotCorrection.setLotNumber(rs.getString("Lot_Number"));
                lotCorrection.setProcessType(rs.getString("Process_Type"));
                lotCorrection.setProcessStepId(rs.getString("ProcessStep_ID"));
                lotCorrection.setLocationId(rs.getString("Location_ID"));
                lotCorrection.setQuantityIn(rs.getString("Quantity_In"));
                lotCorrection.setQuantityOut(rs.getString("Quantity_Out"));
                lotCorrection.setDateIn(rs.getString("Date_In"));
                lotCorrection.setDateOut(rs.getString("Date_Out"));
                lotCorrection.setDateCode(rs.getString("Date_Code"));
                lotCorrection.setDieSize(rs.getString("Die_Size"));
                lotCorrection.setGrossDie(rs.getString("Gross_Die"));
                lotCorrection.setLocationName(rs.getString("L_Vendor_Name") + "/" + rs.getString("City"));
                lotCorrectionList.add(lotCorrection);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException("Error while retriving the next lot correction list", e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException se) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException se) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException se) {
                }
            }
        }
        return lotCorrectionList;
    }

    /**
     * Saves the lot correction object in the database
     *
     * @param dbTransaction
     * @param lotCorrection
     * @param isNextStageInformation ( To indicate whether this lot correction is the next stage info )
     * @throws ScalarException
     */
    public static void saveLotCorrection(DBTransaction dbTransaction, LotCorrection lotCorrection, boolean isNextStageInformation) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection(Constants.DATASOURCE_TYPE_LOCAL);
            } else {
                conn = dbTransaction.getConnection(Constants.DATASOURCE_TYPE_LOCAL);
            }

            pstmt = conn.prepareStatement("{call  sp_save_lot_correction( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ? ) }");
            pstmt.setString(1, lotCorrection.getLotId());
            pstmt.setString(2, lotCorrection.getLotNumber());
            pstmt.setString(3, lotCorrection.getLotStatus());
            pstmt.setString(4, lotCorrection.getProcessType());
            pstmt.setString(5, lotCorrection.getProcessStepId());
            pstmt.setString(6, lotCorrection.getLocationId());
            pstmt.setString(7, lotCorrection.getQuantityIn());
            pstmt.setString(8, lotCorrection.getQuantityOut());
            pstmt.setString(9, lotCorrection.getDateIn());
            pstmt.setString(10, lotCorrection.getDateOut());
            pstmt.setString(11, lotCorrection.getDateCode());
            pstmt.setString(12, lotCorrection.getGrossDie());
            pstmt.setString(13, lotCorrection.getDieSize());
            pstmt.setString(14, lotCorrection.getVendorLotNumber());
            pstmt.setString(15, lotCorrection.getLotComments());
            pstmt.setBoolean(16, isNextStageInformation);
            pstmt.setBoolean(17, lotCorrection.isHold());
            pstmt.executeUpdate();
            success = true;
        } catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException("Error while retriving the next lot correction list", e);
        } finally {
            if ((dbTransaction == null) && (conn != null)) {
                if (success) {
                    try {
                        conn.commit();
                    } catch (Exception e) {
                        e.printStackTrace();
                        throw new ScalarException("Error while commiting lot correction save", e);
                    }
                } else {
                    try {
                        conn.rollback();
                    } catch (Exception e) {
                        e.printStackTrace();
                        throw new ScalarException("Error while rolling back lot correction save", e);
                    }
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException se) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException se) {
                }
            }
        }

    }

    /**
     * Saves the lot correction object in the database
     *
     * @param dbTransaction
     * @param lotCorrection
     * @throws ScalarException
     */
    public static boolean checkDuplicateLots(DBTransaction dbTransaction, LotCorrection lotCorrection) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection(Constants.DATASOURCE_TYPE_LOCAL);
            } else {
                conn = dbTransaction.getConnection(Constants.DATASOURCE_TYPE_LOCAL);
            }

            pstmt = conn.prepareStatement("select lot_number from lot_table where lot_id <> ? and lot_number = ? and processstep_id = ? and location_id = ?");
            pstmt.setString(1, lotCorrection.getLotId());
            pstmt.setString(2, lotCorrection.getLotNumber());
            pstmt.setString(3, lotCorrection.getProcessStepId());
            pstmt.setString(4, lotCorrection.getLocationId());

            rs = pstmt.executeQuery();

            if (rs.next()) {
                success = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new ScalarException("Error while checking for duplicate lots", e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException se) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException se) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException se) {
                }
            }
        }

        return success;
    }


    /**
     * Returns a list of Packingslip Line Items objects which are present for a lot.
     *
     * @param lotId
     * @return
     * @throws ScalarException
     */
    public static HashMap loadPackingSlipsByLotId( DBTransaction dbTransaction, String lotId ) throws ScalarException
    {
        PackingSlipLineItem psLineItems = null;
        PackingSlipForm packingSlipForm = null;
        CustomerShippingLineItems cslt = null;
        HashMap packingSlipsMap = new HashMap();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try
        {
            if (dbTransaction == null) {
                conn = DataSource.getConnection(Constants.DATASOURCE_TYPE_LOCAL);
            } else {
                conn = dbTransaction.getConnection(Constants.DATASOURCE_TYPE_LOCAL);
            }

            //pstmt 	= conn.prepareCall ( "SELECT CST.CustomerShipping_ID, CST.Label, CST.SalesOrder_ID, CST.CST_Status FROM Customer_Shipping_Table CST INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CST.CustomerShipping_ID = CSLIT.CustomerShipping_ID INNER JOIN Customer_Shipping_Lot_Number_Table CSLNT ON CSLIT.CSLIT_ID = CSLNT.CSLIT_ID WHERE CSLNT.Lot_ID = ? AND CST.CST_Status = 'ISSUED'" );
            pstmt 	= conn.prepareCall ( "SELECT CST.CustomerShipping_ID, CST.Label, CST.SalesOrder_ID, CST.CST_Status, SO.SalesOrder_No, CST.[Timestamp] As GeneratedDate  FROM Customer_Shipping_Table CST INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CST.CustomerShipping_ID = CSLIT.CustomerShipping_ID INNER JOIN Customer_Shipping_Lot_Number_Table CSLNT ON CSLIT.CSLIT_ID = CSLNT.CSLIT_ID INNER JOIN Sales_Order SO ON CST.SalesOrder_ID = SO.SalesOrder_ID WHERE CSLNT.CSLNT_Qty_Pull > 0 AND CSLNT.Lot_ID = ? AND CST.CST_Status = 'ISSUED'");
			pstmt.setString ( 1, lotId );
			rs 	= pstmt.executeQuery();


			while ( rs.next() )
			{
                packingSlipForm = new PackingSlipForm();
                packingSlipForm.setCustomerShippingId(  rs.getInt("CustomerShipping_ID") );
                packingSlipForm.setSalesOrderId  (  rs.getInt("SalesOrder_ID") );
                packingSlipForm.setSalesOrderNumber (  rs.getString("SalesOrder_No") );
                packingSlipForm.setGeneratedDate( rs.getDate ("GeneratedDate") );
                packingSlipForm.setLabel  (  rs.getString("Label") );
                //System.out.println("--------------------1---LC-DAO-HASHMAP-cslt = " + rs.getString("CustomerShipping_ID"));
				packingSlipsMap.put ( rs.getString("CustomerShipping_ID") , packingSlipForm );
			}

			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

            String cstId = null;
            PackingSlipForm tempPSForm = null;
            ArrayList lineItems = null;

            //pstmt = conn.prepareStatement("SELECT CSLNT.CSLNT_Qty_Pull, CSLIT.CSLIT_Qty_Shipped, CSLIT.LineItem_ID, CSLIT.CSLIT_ID, CST.CustomerShipping_ID, CST.Label, CST.SalesOrder_ID, CST.CST_Status, P.Product_ID, P.Part_Number, COOL.COOL_Line_Item_No, SO.SalesOrder_No, CONVERT(VARCHAR, CST.[Timestamp], 101) AS Ps_Created_On FROM Customer_Shipping_Lot_Number_Table CSLNT INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CSLNT.CSLIT_ID = CSLIT.CSLIT_ID INNER JOIN Customer_Shipping_Table CST ON CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID INNER JOIN Customer_Open_Order_LineItem COOL ON CSLIT.LineItem_ID = COOL.LineItem_ID INNER JOIN Product P ON COOL.Product_ID = P.Product_ID INNER JOIN Sales_Order SO ON CST.SalesOrder_ID = SO.SalesOrder_ID AND COOL.SalesOrder_ID = SO.SalesOrder_ID WHERE CST.CST_Status = 'ISSUED' AND CSLNT.CSLNT_Qty_Pull > 0 AND CST.CustomerShipping_ID = ? AND CSLNT.Lot_ID = ?");
            pstmt = conn.prepareStatement("SELECT CSLNT.CSLNT_Qty_Pull, CSLIT.CSLIT_Qty_Shipped, CSLIT.LineItem_ID, CSLIT.CSLIT_ID, P.Product_ID, P.Part_Number, COOL.COOL_Line_Item_No, CSLIT.CustomerShipping_ID FROM Customer_Shipping_Lot_Number_Table CSLNT INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CSLNT.CSLIT_ID = CSLIT.CSLIT_ID INNER JOIN Customer_Open_Order_LineItem COOL ON CSLIT.LineItem_ID = COOL.LineItem_ID INNER JOIN Product P ON COOL.Product_ID = P.Product_ID WHERE CSLNT.CSLNT_Qty_Pull > 0 AND CSLIT.CustomerShipping_ID = ? AND CSLNT.Lot_ID = ?");

            Iterator it = packingSlipsMap.keySet().iterator();

            while (it.hasNext())
            {
                cstId = (String) it.next();
                //System.out.println("-----------------2------LC-DAO-HASHMAP-cslt = " + cstId);
                tempPSForm = (PackingSlipForm) packingSlipsMap.get( cstId + "" );
                if (tempPSForm != null)
                {
                    pstmt.setString(1, cstId);
                    pstmt.setString(2, lotId);
                    rs = pstmt.executeQuery();
                    lineItems = tempPSForm.getLineitems();
                    if ( lineItems == null )
                    {
                        lineItems = new ArrayList();
                    }
                    while( rs.next() )
                    {
                        psLineItems = new PackingSlipLineItem();
                        psLineItems.setLotQtyPulled         ( rs.getInt("CSLNT_Qty_Pull") );
                        psLineItems.setLineItemQtyPulled    ( rs.getInt("CSLIT_Qty_Shipped") );
                        psLineItems.setCsLineItemId         ( rs.getInt("CSLIT_ID") );
                        psLineItems.setLineItemId           ( rs.getInt("LineItem_ID") );
                        psLineItems.setLineItemNumber       ( rs.getInt("COOL_Line_Item_No") );
                        psLineItems.setPartNumber           ( rs.getString("Part_Number") );
                        //psLineItems.setPackingSlipCreateOn  ( rs.getString("Ps_Created_On") );
                        //System.out.println("-----------------2------LC-DAO-HASHMAP-cslt = " + psLineItems.getCsLineItemId());
                        lineItems.add( psLineItems );
                    }
                    tempPSForm.setLineitems( lineItems );
                }
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Error while retriving the PS Line Item for Lot Id " + lotId, e);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException se) { }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException se) { }
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException se) { }
            }
        }
        return packingSlipsMap;
    }

    /**
     * return the true if PS Line Items are present for a particular Lot Id.
     *
     * @param dbTransaction
     * @param lotId
     * @throws ScalarException
     */
    public static boolean isLineItemsExitsByLotId ( DBTransaction dbTransaction, String lotId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int tempLotId = 0;

        try
        {
            if (dbTransaction == null) {
                conn = DataSource.getConnection(Constants.DATASOURCE_TYPE_LOCAL);
            } else {
                conn = dbTransaction.getConnection(Constants.DATASOURCE_TYPE_LOCAL);
            }
            //System.out.println("-----------------LC-DAO----lotId = " + lotId );
            try
            {
                tempLotId = Integer.parseInt( lotId ) ;
            }
            catch (NumberFormatException nfe) {}

            pstmt = conn.prepareStatement("SELECT COUNT(CSLIT.LineItem_ID) AS TotalLineItems FROM Lot_Table LT INNER JOIN Customer_Shipping_Lot_Number_Table CSLNT ON LT.Lot_ID = CSLNT.Lot_ID INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CSLNT.CSLIT_ID = CSLIT.CSLIT_ID INNER JOIN Customer_Shipping_Table CST ON CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID WHERE LT.Lot_ID = ? AND CST.CST_Status = 'ISSUED' AND CSLNT.CSLNT_Qty_Pull > 0");

            pstmt.setInt( 1, tempLotId );
            rs = pstmt.executeQuery();

            if ( rs.next() )
            {
                //System.out.println("-----------------LC-DAO----TotalLineItems = " + rs.getInt( "TotalLineItems" ) );
                if ( rs.getInt( "TotalLineItems" ) > 0 )
                {
                    success = true;
                }
                else
                {
                    success = false;
                }
            }
            else
            {
                success = false;
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Error while retriving the PS Line Item count for Lot Id " + lotId, e);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException se) { }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException se) { }
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException se) { }
            }
        }
        return success;
    }

     /**
     * Reverting the lot qty in the database
     *
     * @param dbTransaction
     * @param selectedCsLineItemIds
     * @param lotId
     * @throws ScalarException
     */
    public static void cancelPackingSlips( DBTransaction dbTransaction, String[] selectedCsLineItemIds, String lotId ) throws ScalarException {
        Connection conn = null;
        CallableStatement cstmt = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean success = false;
        int tempCSId = -1;
        int selectedCsLineItemIdsLength = 0;

        if( selectedCsLineItemIds != null && selectedCsLineItemIds.length > 0 )
        {
            selectedCsLineItemIdsLength = selectedCsLineItemIds.length;
        }
        try
        {
            if (dbTransaction == null) {
                conn = DataSource.getConnection(Constants.DATASOURCE_TYPE_LOCAL);
            } else {
                conn = dbTransaction.getConnection(Constants.DATASOURCE_TYPE_LOCAL);
            }

            cstmt = conn.prepareCall ( SqlQueryMappings.get ( "operations.packingslip.cancelPackingSlip" ) );

            for ( int i = 0; i < selectedCsLineItemIdsLength; i++ )
            {
                try
                {
                    tempCSId = Integer.parseInt( selectedCsLineItemIds[i] ) ;
                }
                catch (NumberFormatException nfe) { tempCSId = -1; }

                cstmt.setInt 	( 1, tempCSId );
                cstmt.setString ( 2, "CANCELLED" );
                cstmt.setString ( 3, "admin" );//need to chage
                cstmt.addBatch();
            }
            cstmt.executeBatch();
            cstmt.clearBatch();
            success = true;
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new ScalarException("Error while packingslip cancenllation", e);
        }
        finally
        {
            if ((dbTransaction == null) && (conn != null)) {
                if (success) {
                    try {
                        conn.commit();
                    } catch (Exception e) {
                        e.printStackTrace();
                        throw new ScalarException("Error while commiting of 'packingslip cancenllation'", e);
                    }
                } else {
                    try {
                        conn.rollback();
                    } catch (Exception e) {
                        e.printStackTrace();
                        throw new ScalarException("Error while rollbacking of 'packingslip cancenllation'", e);
                    }
                }
            }
            if (rs != null) {
                try { rs.close(); } catch (SQLException se) { }
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException se) { }
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException se) { }
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException se) { }
            }
        }

    }

}
